package org.example.xsda.service;

import cn.dev33.satoken.stp.StpUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.example.xsda.dto.ImportResultDTO;
import org.example.xsda.dto.ScoreExcelDTO;
import org.example.xsda.dto.StudentExcelDTO;
import org.example.xsda.entity.*;
import org.example.xsda.exception.BusinessException;
import org.example.xsda.mapper.*;
import org.example.xsda.vo.ImportHistoryVO;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Slf4j
@Service
public class ImportExportService {
    
    @Resource
    private ImportHistoryMapper importHistoryMapper;
    
    @Resource
    private StudentMapper studentMapper;
    
    @Resource
    private StudentRecordMapper studentRecordMapper;
    
    @Resource
    private StudentScoreMapper studentScoreMapper;
    
    @Resource
    private StudentScoreMapper scoreMapper;
    
    @Resource
    private SysUserMapper sysUserMapper;
    
    @Resource
    private DeptMapper deptMapper;
    
    @Resource
    private ClassesMapper classesMapper;
    
    @Resource
    private SysUserMapper userMapper;
    
    /**
     * 导入学生信息
     */
    @Transactional(rollbackFor = Exception.class)
    public ImportResultDTO importStudent(MultipartFile file) {
        ImportHistory history = new ImportHistory();
        history.setType("student");
        history.setFileName(file.getOriginalFilename());
        history.setStatus(0);
        history.setOperatorId(StpUtil.getLoginIdAsLong());
        importHistoryMapper.insert(history);
        
        try {
            // 读取院系和班级映射
            Map<String, Long> deptMap = new HashMap<>();
            List<Dept> depts = deptMapper.selectList(null);
            for (Dept dept : depts) {
                deptMap.put(dept.getDeptName(), dept.getId());
            }
            
            Map<String, Long> classMap = new HashMap<>();
            List<Classes> classes = classesMapper.selectList(null);
            for (Classes cls : classes) {
                classMap.put(cls.getClassName(), cls.getId());
            }
            
            List<StudentExcelDTO> successList = new ArrayList<>();
            List<StudentExcelDTO> failList = new ArrayList<>();
            
            // 读取Excel
            EasyExcel.read(file.getInputStream(), StudentExcelDTO.class, new AnalysisEventListener<StudentExcelDTO>() {
                @Override
                public void invoke(StudentExcelDTO data, AnalysisContext context) {
                    // 记录当前行号（Excel行号 = 数据行号 + 1（表头））
                    Integer rowNumber = context.readRowHolder().getRowIndex() + 1;
                    data.setRowNumber(rowNumber);
                    
                    try {
                        // 验证必填字段
                        if (data.getStudentNo() == null || data.getStudentNo().trim().isEmpty()) {
                            data.setErrorReason("学号不能为空");
                            failList.add(data);
                            log.warn("第{}行导入失败：学号不能为空", rowNumber);
                            return;
                        }
                        
                        if (data.getRealName() == null || data.getRealName().trim().isEmpty()) {
                            data.setErrorReason("姓名不能为空");
                            failList.add(data);
                            log.warn("第{}行导入失败：姓名不能为空", rowNumber);
                            return;
                        }
                        
                        // 检查学号是否已存在（存在则更新，不存在则新建）
                        LambdaQueryWrapper<Student> wrapper = new LambdaQueryWrapper<>();
                        wrapper.eq(Student::getStudentNo, data.getStudentNo());
                        Student student = studentMapper.selectOne(wrapper);
                        
                        boolean isUpdate = false;
                        Long existingUserId = null;
                        
                        if (student != null) {
                            // 学号已存在，准备更新
                            isUpdate = true;
                            existingUserId = student.getUserId();
                            log.info("第{}行：学号{}已存在，将进行覆盖更新", rowNumber, data.getStudentNo());
                        } else {
                            // 学号不存在，创建新记录
                            student = new Student();
                            student.setStudentNo(data.getStudentNo());
                        }
                        
                        // 性别转换
                        if ("男".equals(data.getGender())) {
                            student.setGender(1);
                        } else if ("女".equals(data.getGender())) {
                            student.setGender(2);
                        } else if (data.getGender() != null && !data.getGender().trim().isEmpty()) {
                            data.setErrorReason("性别格式错误，应为'男'或'女'");
                            failList.add(data);
                            log.warn("第{}行导入失败：性别格式错误 - {}", rowNumber, data.getGender());
                            return;
                        }
                        
                        // 日期转换
                        try {
                            if (data.getBirthday() != null && !data.getBirthday().trim().isEmpty()) {
                                student.setBirthday(LocalDate.parse(data.getBirthday(), DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                            }
                        } catch (Exception e) {
                            data.setErrorReason("出生日期格式错误，应为'yyyy-MM-dd'格式，如'2005-03-15'");
                            failList.add(data);
                            log.warn("第{}行导入失败：出生日期格式错误 - {}", rowNumber, data.getBirthday());
                            return;
                        }
                        
                        try {
                            if (data.getEnrollDate() != null && !data.getEnrollDate().trim().isEmpty()) {
                                student.setEnrollDate(LocalDate.parse(data.getEnrollDate(), DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                            }
                        } catch (Exception e) {
                            data.setErrorReason("入学日期格式错误，应为'yyyy-MM-dd'格式，如'2023-09-01'");
                            failList.add(data);
                            log.warn("第{}行导入失败：入学日期格式错误 - {}", rowNumber, data.getEnrollDate());
                            return;
                        }
                        
                        // 院系和班级
                        if (data.getDeptName() != null && !data.getDeptName().trim().isEmpty()) {
                            Long deptId = deptMap.get(data.getDeptName());
                            if (deptId == null) {
                                data.setErrorReason("院系[" + data.getDeptName() + "]不存在，请先在系统中创建");
                                failList.add(data);
                                log.warn("第{}行导入失败：院系不存在 - {}", rowNumber, data.getDeptName());
                                return;
                            }
                            student.setDeptId(deptId);
                        }
                        
                        if (data.getClassName() != null && !data.getClassName().trim().isEmpty()) {
                            Long classId = classMap.get(data.getClassName());
                            if (classId == null) {
                                data.setErrorReason("班级[" + data.getClassName() + "]不存在，请先在系统中创建");
                                failList.add(data);
                                log.warn("第{}行导入失败：班级不存在 - {}", rowNumber, data.getClassName());
                                return;
                            }
                            student.setClassId(classId);
                        }
                        
                        // 状态转换
                        if ("在籍".equals(data.getStatus())) {
                            student.setStatus(1);
                        } else if ("休学".equals(data.getStatus())) {
                            student.setStatus(2);
                        } else if ("毕业".equals(data.getStatus())) {
                            student.setStatus(3);
                        } else if ("退学".equals(data.getStatus())) {
                            student.setStatus(4);
                        } else if (data.getStatus() != null && !data.getStatus().trim().isEmpty()) {
                            data.setErrorReason("学籍状态格式错误，应为'在籍'、'休学'、'毕业'或'退学'");
                            failList.add(data);
                            log.warn("第{}行导入失败：学籍状态格式错误 - {}", rowNumber, data.getStatus());
                            return;
                        }
                        
                        student.setRemark(data.getRemark());
                        
                        // 处理用户账号
                        if (isUpdate) {
                            // 更新模式：更新现有用户信息
                            if (existingUserId != null) {
                                SysUser user = userMapper.selectById(existingUserId);
                                if (user != null) {
                                    user.setRealName(data.getRealName());
                                    // 用户名不变，保持原学号
                                    // 密码不变，保留原密码
                                    userMapper.updateById(user);
                                    log.info("更新用户账号：userId={}, realName={}", user.getId(), user.getRealName());
                                }
                            }
                            // 更新学生记录
                            studentMapper.updateById(student);
                            log.info("第{}行导入成功（更新）：学号 - {}", rowNumber, data.getStudentNo());
                        } else {
                            // 新建模式：创建新的用户账号和学生记录
                            SysUser user = new SysUser();
                            user.setUsername(data.getStudentNo());
                            user.setPassword("$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVKIUi"); // 默认密码123456
                            user.setRealName(data.getRealName());
                            user.setRoleId(3L); // 学生角色
                            user.setStatus(1);
                            user.setNeedChangePassword(1); // 首次登录需要修改密码
                            userMapper.insert(user);
                            
                            student.setUserId(user.getId());
                            student.setInfoCompleted(0); // 标记为未完善个人信息
                            studentMapper.insert(student);
                            log.info("第{}行导入成功（新建）：学号 - {}", rowNumber, data.getStudentNo());
                        }
                        
                        successList.add(data);
                    } catch (Exception e) {
                        data.setErrorReason("系统错误：" + e.getMessage());
                        failList.add(data);
                        log.error("第{}行导入失败：系统错误", rowNumber, e);
                    }
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                }
            }).sheet().doRead();
            
            // 生成失败数据文件（如果有失败记录）
            String failFileUrl = null;
            if (!failList.isEmpty()) {
                failFileUrl = generateFailFileForStudent(failList, history.getId());
            }
            
            // 更新导入历史
            history.setTotalCount(successList.size() + failList.size());
            history.setSuccessCount(successList.size());
            history.setFailCount(failList.size());
            history.setFailFileUrl(failFileUrl);
            history.setStatus(1);
            importHistoryMapper.updateById(history);
            
            // 构建返回结果
            return buildStudentImportResult(successList.size(), failList, failFileUrl);
            
        } catch (Exception e) {
            history.setStatus(2);
            importHistoryMapper.updateById(history);
            throw new RuntimeException("导入失败：" + e.getMessage());
        }
    }
    
    /**
     * 导入成绩数据
     */
    @Transactional(rollbackFor = Exception.class)
    public ImportResultDTO importScore(MultipartFile file) {
        ImportHistory history = new ImportHistory();
        history.setType("score");
        history.setFileName(file.getOriginalFilename());
        history.setStatus(0);
        history.setOperatorId(StpUtil.getLoginIdAsLong());
        importHistoryMapper.insert(history);
        
        try {
            // 读取学生映射
            Map<String, Long> studentMap = new HashMap<>();
            List<Student> students = studentMapper.selectList(null);
            for (Student student : students) {
                studentMap.put(student.getStudentNo(), student.getId());
            }
            
            List<ScoreExcelDTO> successList = new ArrayList<>();
            List<ScoreExcelDTO> failList = new ArrayList<>();
            
            // 读取Excel
            EasyExcel.read(file.getInputStream(), ScoreExcelDTO.class, new AnalysisEventListener<ScoreExcelDTO>() {
                @Override
                public void invoke(ScoreExcelDTO data, AnalysisContext context) {
                    // 记录当前行号（Excel行号 = 数据行号 + 1（表头））
                    Integer rowNumber = context.readRowHolder().getRowIndex() + 1;
                    data.setRowNumber(rowNumber);
                    
                    try {
                        // 验证必填字段
                        if (data.getStudentNo() == null || data.getStudentNo().trim().isEmpty()) {
                            data.setErrorReason("学号不能为空");
                            failList.add(data);
                            log.warn("第{}行成绩导入失败：学号不能为空", rowNumber);
                            return;
                        }
                        
                        if (data.getCourseName() == null || data.getCourseName().trim().isEmpty()) {
                            data.setErrorReason("课程名称不能为空");
                            failList.add(data);
                            log.warn("第{}行成绩导入失败：课程名称不能为空", rowNumber);
                            return;
                        }
                        
                        if (data.getScore() == null || data.getScore().trim().isEmpty()) {
                            data.setErrorReason("成绩不能为空");
                            failList.add(data);
                            log.warn("第{}行成绩导入失败：成绩不能为空", rowNumber);
                            return;
                        }
                        
                        // 查找学生ID
                        Long studentId = studentMap.get(data.getStudentNo());
                        if (studentId == null) {
                            data.setErrorReason("学号[" + data.getStudentNo() + "]不存在，请先导入学生信息");
                            failList.add(data);
                            log.warn("第{}行成绩导入失败：学号不存在 - {}", rowNumber, data.getStudentNo());
                            return;
                        }
                        
                        // 验证成绩格式
                        try {
                            new java.math.BigDecimal(data.getScore());
                        } catch (Exception e) {
                            data.setErrorReason("成绩格式错误，应为数字，如'85'或'92.5'");
                            failList.add(data);
                            log.warn("第{}行成绩导入失败：成绩格式错误 - {}", rowNumber, data.getScore());
                            return;
                        }
                        
                        // 验证学分格式
                        if (data.getCredit() != null && !data.getCredit().trim().isEmpty()) {
                            try {
                                new java.math.BigDecimal(data.getCredit());
                            } catch (Exception e) {
                                data.setErrorReason("学分格式错误，应为数字，如'4.0'或'3.5'");
                                failList.add(data);
                                log.warn("第{}行成绩导入失败：学分格式错误 - {}", rowNumber, data.getCredit());
                                return;
                            }
                        }
                        
                        // 检查是否已存在相同的成绩记录（学生ID+课程名称+学期）
                        LambdaQueryWrapper<StudentScore> scoreWrapper = new LambdaQueryWrapper<>();
                        scoreWrapper.eq(StudentScore::getStudentId, studentId)
                                   .eq(StudentScore::getCourseName, data.getCourseName());
                        if (data.getSemester() != null && !data.getSemester().trim().isEmpty()) {
                            scoreWrapper.eq(StudentScore::getSemester, data.getSemester());
                        }
                        StudentScore existingScore = scoreMapper.selectOne(scoreWrapper);
                        
                        StudentScore score;
                        boolean isUpdate = false;
                        
                        if (existingScore != null) {
                            // 已存在，更新
                            score = existingScore;
                            isUpdate = true;
                            log.info("第{}行：成绩已存在，将进行覆盖更新 - 学号:{}, 课程:{}", rowNumber, data.getStudentNo(), data.getCourseName());
                        } else {
                            // 不存在，新建
                            score = new StudentScore();
                            score.setStudentId(studentId);
                            score.setCourseName(data.getCourseName());
                        }
                        
                        // 设置/更新成绩信息
                        score.setScore(new java.math.BigDecimal(data.getScore()));
                        score.setCredit(data.getCredit() != null && !data.getCredit().trim().isEmpty() 
                            ? new java.math.BigDecimal(data.getCredit()) : null);
                        score.setSemester(data.getSemester());
                        
                        if (isUpdate) {
                            scoreMapper.updateById(score);
                            log.info("第{}行成绩导入成功（更新）：学号 - {}, 课程 - {}", rowNumber, data.getStudentNo(), data.getCourseName());
                        } else {
                            scoreMapper.insert(score);
                            log.info("第{}行成绩导入成功（新建）：学号 - {}, 课程 - {}", rowNumber, data.getStudentNo(), data.getCourseName());
                        }
                        
                        successList.add(data);
                    } catch (Exception e) {
                        data.setErrorReason("系统错误：" + e.getMessage());
                        failList.add(data);
                        log.error("第{}行成绩导入失败：系统错误", rowNumber, e);
                    }
                }
                
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                }
            }).sheet().doRead();
            
            // 生成失败数据文件（如果有失败记录）
            String failFileUrl = null;
            if (!failList.isEmpty()) {
                failFileUrl = generateFailFileForScore(failList, history.getId());
            }
            
            // 更新导入历史
            history.setTotalCount(successList.size() + failList.size());
            history.setSuccessCount(successList.size());
            history.setFailCount(failList.size());
            history.setFailFileUrl(failFileUrl);
            history.setStatus(1);
            importHistoryMapper.updateById(history);
            
            // 构建返回结果
            return buildScoreImportResult(successList.size(), failList, failFileUrl);
            
        } catch (Exception e) {
            history.setStatus(2);
            importHistoryMapper.updateById(history);
            throw new RuntimeException("导入失败：" + e.getMessage());
        }
    }
    
    /**
     * 获取导入历史
     */
    public List<ImportHistoryVO> getImportHistory() {
        LambdaQueryWrapper<ImportHistory> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(ImportHistory::getOperatorId, StpUtil.getLoginIdAsLong());
        wrapper.orderByDesc(ImportHistory::getCreateTime);
        
        List<ImportHistory> historyList = importHistoryMapper.selectList(wrapper);
        
        // 转换为VO并关联操作人姓名
        List<ImportHistoryVO> voList = new ArrayList<>();
        for (ImportHistory history : historyList) {
            ImportHistoryVO vo = new ImportHistoryVO();
            vo.setId(history.getId());
            vo.setType(history.getType());
            vo.setFileName(history.getFileName());
            vo.setTotalCount(history.getTotalCount());
            vo.setSuccessCount(history.getSuccessCount());
            vo.setFailCount(history.getFailCount());
            vo.setStatus(history.getStatus());
            vo.setFailFileUrl(history.getFailFileUrl());
            vo.setOperatorId(history.getOperatorId());
            vo.setCreateTime(history.getCreateTime());
            vo.setUpdateTime(history.getUpdateTime());
            
            // 查询操作人姓名
            if (history.getOperatorId() != null) {
                SysUser operator = userMapper.selectById(history.getOperatorId());
                if (operator != null) {
                    vo.setOperatorName(operator.getRealName());
                }
            }
            
            voList.add(vo);
        }
        
        return voList;
    }
    
    /**
     * 下载导入模板（带测试样例）
     */
    public void downloadTemplate(String type, HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            
            if ("student".equals(type)) {
                // 学生信息模板
                String fileName = URLEncoder.encode("学生信息导入模板(含测试样例)", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
                
                // 创建测试样例数据
                List<StudentExcelDTO> sampleData = new ArrayList<>();
                
                // 样例1 - 软件工程1班（学号：年份+班级编号+序号）
                StudentExcelDTO sample1 = new StudentExcelDTO();
                sample1.setStudentNo("2024001011");
                sample1.setRealName("张三");
                sample1.setGender("男");
                sample1.setBirthday("2005-03-15");
                sample1.setEnrollDate("2023-09-01");
                sample1.setDeptName("软件工程系");
                sample1.setClassName("2023级软件工程1班");
                sample1.setStatus("在籍");
                sample1.setRemark("优秀学生");
                sampleData.add(sample1);
                
                // 样例2 - 软件工程1班
                StudentExcelDTO sample2 = new StudentExcelDTO();
                sample2.setStudentNo("2024001012");
                sample2.setRealName("李四");
                sample2.setGender("女");
                sample2.setBirthday("2005-06-20");
                sample2.setEnrollDate("2023-09-01");
                sample2.setDeptName("软件工程系");
                sample2.setClassName("2023级软件工程1班");
                sample2.setStatus("在籍");
                sample2.setRemark("班长");
                sampleData.add(sample2);
                
                // 样例3 - 软件工程2班
                StudentExcelDTO sample3 = new StudentExcelDTO();
                sample3.setStudentNo("2024002011");
                sample3.setRealName("王五");
                sample3.setGender("男");
                sample3.setBirthday("2005-08-10");
                sample3.setEnrollDate("2023-09-01");
                sample3.setDeptName("软件工程系");
                sample3.setClassName("2023级软件工程2班");
                sample3.setStatus("在籍");
                sample3.setRemark("学习委员");
                sampleData.add(sample3);
                
                // 样例4 - 软件工程2班
                StudentExcelDTO sample4 = new StudentExcelDTO();
                sample4.setStudentNo("2024002012");
                sample4.setRealName("赵六");
                sample4.setGender("女");
                sample4.setBirthday("2005-12-25");
                sample4.setEnrollDate("2023-09-01");
                sample4.setDeptName("软件工程系");
                sample4.setClassName("2023级软件工程2班");
                sample4.setStatus("在籍");
                sample4.setRemark("体育委员");
                sampleData.add(sample4);
                
                // 样例5 - 网络工程1班
                StudentExcelDTO sample5 = new StudentExcelDTO();
                sample5.setStudentNo("2024003011");
                sample5.setRealName("钱七");
                sample5.setGender("男");
                sample5.setBirthday("2005-05-18");
                sample5.setEnrollDate("2023-09-01");
                sample5.setDeptName("网络工程系");
                sample5.setClassName("2023级网络工程1班");
                sample5.setStatus("在籍");
                sample5.setRemark("团支书");
                sampleData.add(sample5);
                
                // 写入Excel
                EasyExcel.write(response.getOutputStream(), StudentExcelDTO.class)
                    .sheet("学生信息")
                    .doWrite(sampleData);
                    
            } else if ("score".equals(type)) {
                // 成绩信息模板
                String fileName = URLEncoder.encode("成绩信息导入模板(含测试样例)", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
                
                // 创建测试样例数据（使用与学生模板匹配的10位学号）
                List<ScoreExcelDTO> sampleData = new ArrayList<>();
                
                // 样例1 - 张三的高等数学成绩
                ScoreExcelDTO sample1 = new ScoreExcelDTO();
                sample1.setStudentNo("2024001011");
                sample1.setStudentName("张三");
                sample1.setCourseName("高等数学");
                sample1.setScore("92");
                sample1.setCredit("4.0");
                sample1.setSemester("2024-2025第一学期");
                sample1.setExamType("期末考试");
                sample1.setRemark("优秀");
                sampleData.add(sample1);
                
                // 样例2 - 张三的大学英语成绩
                ScoreExcelDTO sample2 = new ScoreExcelDTO();
                sample2.setStudentNo("2024001011");
                sample2.setStudentName("张三");
                sample2.setCourseName("大学英语");
                sample2.setScore("88");
                sample2.setCredit("3.0");
                sample2.setSemester("2024-2025第一学期");
                sample2.setExamType("期末考试");
                sample2.setRemark("良好");
                sampleData.add(sample2);
                
                // 样例3 - 李四的高等数学成绩
                ScoreExcelDTO sample3 = new ScoreExcelDTO();
                sample3.setStudentNo("2024001012");
                sample3.setStudentName("李四");
                sample3.setCourseName("高等数学");
                sample3.setScore("85");
                sample3.setCredit("4.0");
                sample3.setSemester("2024-2025第一学期");
                sample3.setExamType("期末考试");
                sample3.setRemark("良好");
                sampleData.add(sample3);
                
                // 样例4 - 王五的数据结构成绩
                ScoreExcelDTO sample4 = new ScoreExcelDTO();
                sample4.setStudentNo("2024002011");
                sample4.setStudentName("王五");
                sample4.setCourseName("数据结构");
                sample4.setScore("90");
                sample4.setCredit("4.0");
                sample4.setSemester("2024-2025-1");
                sample4.setExamType("期末考试");
                sample4.setRemark("优秀");
                sampleData.add(sample4);
                
                // 样例5 - 赵六的计算机网络成绩
                ScoreExcelDTO sample5 = new ScoreExcelDTO();
                sample5.setStudentNo("2024002012");
                sample5.setStudentName("赵六");
                sample5.setCourseName("计算机网络");
                sample5.setScore("87");
                sample5.setCredit("3.0");
                sample5.setSemester("2024-2025-1");
                sample5.setExamType("期末考试");
                sample5.setRemark("良好");
                sampleData.add(sample5);
                
                // 写入Excel
                EasyExcel.write(response.getOutputStream(), ScoreExcelDTO.class)
                    .sheet("成绩数据")
                    .doWrite(sampleData);
            } else {
                throw new BusinessException("不支持的模板类型");
            }
            
        } catch (IOException e) {
            throw new RuntimeException("下载模板失败：" + e.getMessage());
        }
    }
    
    /**
     * 导出学生信息
     */
    public void exportStudent(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("学生信息", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            
            // 查询所有学生
            List<Student> students = studentMapper.selectList(null);
            
            // 查询院系和班级
            List<Dept> depts = deptMapper.selectList(null);
            Map<Long, String> deptMap = new HashMap<>();
            for (Dept dept : depts) {
                deptMap.put(dept.getId(), dept.getDeptName());
            }
            
            List<Classes> classes = classesMapper.selectList(null);
            Map<Long, String> classMap = new HashMap<>();
            for (Classes cls : classes) {
                classMap.put(cls.getId(), cls.getClassName());
            }
            
            // 查询用户（获取姓名）
            List<Long> userIds = new ArrayList<>();
            for (Student student : students) {
                if (student.getUserId() != null) {
                    userIds.add(student.getUserId());
                }
            }
            List<SysUser> users = userIds.isEmpty() ? new ArrayList<>() : userMapper.selectBatchIds(userIds);
            Map<Long, String> userMap = new HashMap<>();
            for (SysUser user : users) {
                userMap.put(user.getId(), user.getRealName());
            }
            
            // 转换为Excel DTO
            List<StudentExcelDTO> excelList = new ArrayList<>();
            for (Student student : students) {
                StudentExcelDTO dto = new StudentExcelDTO();
                dto.setStudentNo(student.getStudentNo());
                dto.setRealName(userMap.get(student.getUserId()));
                dto.setGender(student.getGender() != null && student.getGender() == 1 ? "男" : "女");
                dto.setBirthday(student.getBirthday() != null ? student.getBirthday().toString() : "");
                dto.setEnrollDate(student.getEnrollDate() != null ? student.getEnrollDate().toString() : "");
                dto.setDeptName(deptMap.get(student.getDeptId()));
                dto.setClassName(classMap.get(student.getClassId()));
                
                String status = "未知";
                if (student.getStatus() != null) {
                    switch (student.getStatus()) {
                        case 1: status = "在籍"; break;
                        case 2: status = "休学"; break;
                        case 3: status = "毕业"; break;
                        case 4: status = "退学"; break;
                    }
                }
                dto.setStatus(status);
                dto.setRemark(student.getRemark());
                
                excelList.add(dto);
            }
            
            // 写入Excel
            EasyExcel.write(response.getOutputStream(), StudentExcelDTO.class)
                .sheet("学生信息")
                .doWrite(excelList);
                
        } catch (IOException e) {
            throw new RuntimeException("导出失败：" + e.getMessage());
        }
    }
    
    /**
     * 导出成绩数据
     */
    public void exportScore(String semester, HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("成绩数据_" + semester, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            
            // 查询成绩
            LambdaQueryWrapper<StudentScore> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(semester != null && !semester.isEmpty(), StudentScore::getSemester, semester);
            List<StudentScore> scores = scoreMapper.selectList(wrapper);
            
            // 查询学生
            List<Long> studentIds = new ArrayList<>();
            for (StudentScore score : scores) {
                studentIds.add(score.getStudentId());
            }
            List<Student> students = studentIds.isEmpty() ? new ArrayList<>() : studentMapper.selectBatchIds(studentIds);
            Map<Long, Student> studentMap = new HashMap<>();
            for (Student student : students) {
                studentMap.put(student.getId(), student);
            }
            
            // 查询用户（获取姓名）
            List<Long> userIds = new ArrayList<>();
            for (Student student : students) {
                if (student.getUserId() != null) {
                    userIds.add(student.getUserId());
                }
            }
            List<SysUser> users = userIds.isEmpty() ? new ArrayList<>() : userMapper.selectBatchIds(userIds);
            Map<Long, String> userMap = new HashMap<>();
            for (SysUser user : users) {
                userMap.put(user.getId(), user.getRealName());
            }
            
            // 转换为Excel DTO
            List<ScoreExcelDTO> excelList = new ArrayList<>();
            for (StudentScore score : scores) {
                ScoreExcelDTO dto = new ScoreExcelDTO();
                Student student = studentMap.get(score.getStudentId());
                if (student != null) {
                    dto.setStudentNo(student.getStudentNo());
                    dto.setStudentName(userMap.get(student.getUserId()));
                }
                dto.setCourseName(score.getCourseName());
                dto.setScore(score.getScore() != null ? score.getScore().toString() : "");
                dto.setCredit(score.getCredit() != null ? score.getCredit().toString() : "");
                dto.setSemester(score.getSemester());
                dto.setExamType("");
                dto.setRemark("");
                
                excelList.add(dto);
            }
            
            // 写入Excel
            EasyExcel.write(response.getOutputStream(), ScoreExcelDTO.class)
                .sheet("成绩数据")
                .doWrite(excelList);
                
        } catch (IOException e) {
            throw new RuntimeException("导出失败：" + e.getMessage());
        }
    }
    
    /**
     * 导出学籍汇总表
     */
    public void exportSummary(HttpServletResponse response) {
        try {
            // 查询所有院系
            List<Dept> deptList = deptMapper.selectList(null);
            
            if (deptList == null || deptList.isEmpty()) {
                throw new BusinessException("暂无院系数据，无法导出");
            }
            
            // 统计各院系学生数量
            List<List<Object>> summaryData = new ArrayList<>();
            
            for (Dept dept : deptList) {
                List<Object> row = new ArrayList<>();
                row.add(dept.getDeptName());
                
                // 统计该院系各状态学生数量
                LambdaQueryWrapper<Student> wrapper = new LambdaQueryWrapper<>();
                wrapper.eq(Student::getDeptId, dept.getId());
                
                // 在读
                wrapper.eq(Student::getStatus, 1);
                Long ongoingCount = studentMapper.selectCount(wrapper);
                row.add(ongoingCount);
                
                // 休学
                wrapper = new LambdaQueryWrapper<>();
                wrapper.eq(Student::getDeptId, dept.getId());
                wrapper.eq(Student::getStatus, 2);
                Long suspendedCount = studentMapper.selectCount(wrapper);
                row.add(suspendedCount);
                
                // 毕业
                wrapper = new LambdaQueryWrapper<>();
                wrapper.eq(Student::getDeptId, dept.getId());
                wrapper.eq(Student::getStatus, 3);
                Long graduatedCount = studentMapper.selectCount(wrapper);
                row.add(graduatedCount);
                
                // 退学
                wrapper = new LambdaQueryWrapper<>();
                wrapper.eq(Student::getDeptId, dept.getId());
                wrapper.eq(Student::getStatus, 4);
                Long droppedCount = studentMapper.selectCount(wrapper);
                row.add(droppedCount);
                
                // 总计
                row.add(ongoingCount + suspendedCount + graduatedCount + droppedCount);
                
                summaryData.add(row);
            }
            
            // 导出Excel
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("学籍汇总表_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")), "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            
            EasyExcel.write(response.getOutputStream())
                    .head(buildSummaryHead())
                    .sheet("学籍汇总")
                    .doWrite(summaryData);
                    
            log.info("导出学籍汇总表成功，共{}个院系", deptList.size());
        } catch (Exception e) {
            log.error("导出学籍汇总表失败", e);
            throw new BusinessException("导出失败：" + e.getMessage());
        }
    }
    
    /**
     * 导出档案记录
     */
    public void exportRecord(HttpServletResponse response) {
        try {
            // 查询所有档案记录
            List<StudentRecord> records = studentRecordMapper.selectList(null);
            
            if (records == null || records.isEmpty()) {
                throw new BusinessException("暂无档案记录数据，无法导出");
            }
            
            // 转换为DTO
            List<List<Object>> exportData = new ArrayList<>();
            for (StudentRecord record : records) {
                List<Object> row = new ArrayList<>();
                
                // 获取学生信息
                Student student = studentMapper.selectById(record.getStudentId());
                if (student != null) {
                    row.add(student.getStudentNo());
                    // 从关联的用户表获取姓名
                    SysUser user = sysUserMapper.selectById(student.getUserId());
                    row.add(user != null ? user.getRealName() : "");
                } else {
                    row.add("");
                    row.add("");
                }
                
                row.add(getRecordTypeName(record.getRecordType()));
                row.add(record.getContent() != null ? record.getContent() : "");
                row.add(record.getCreateTime() != null ? 
                        record.getCreateTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : "");
                
                exportData.add(row);
            }
            
            // 导出Excel
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("档案记录_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")), "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            
            EasyExcel.write(response.getOutputStream())
                    .head(buildRecordHead())
                    .sheet("档案记录")
                    .doWrite(exportData);
                    
            log.info("导出档案记录成功，共{}条", exportData.size());
        } catch (Exception e) {
            log.error("导出档案记录失败", e);
            throw new BusinessException("导出失败：" + e.getMessage());
        }
    }
    
    /**
     * 打包毕业档案
     */
    public void exportPackage(String studentNo, HttpServletResponse response) {
        try {
            // 查询学生信息
            LambdaQueryWrapper<Student> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(Student::getStudentNo, studentNo);
            Student student = studentMapper.selectOne(wrapper);
            
            if (student == null) {
                throw new BusinessException("学生不存在");
            }
            
            // 创建临时目录
            String tempDir = System.getProperty("java.io.tmpdir") + File.separator + "archive_" + studentNo;
            File dir = new File(tempDir);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            
            // 1. 导出学生基本信息
            exportStudentInfo(student, tempDir);
            
            // 2. 导出成绩单
            exportStudentScores(student.getId(), tempDir);
            
            // 3. 导出档案记录
            exportStudentRecords(student.getId(), tempDir);
            
            // 4. 打包成ZIP
            String zipFileName = "毕业档案_" + studentNo + "_" + 
                    LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".zip";
            String zipFilePath = tempDir + ".zip";
            
            zipDirectory(tempDir, zipFilePath);
            
            // 5. 下载ZIP文件
            response.setContentType("application/zip");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + 
                    URLEncoder.encode(zipFileName, "UTF-8"));
            
            try (FileInputStream fis = new FileInputStream(zipFilePath);
                 OutputStream os = response.getOutputStream()) {
                byte[] buffer = new byte[1024];
                int len;
                while ((len = fis.read(buffer)) > 0) {
                    os.write(buffer, 0, len);
                }
                os.flush();
            }
            
            // 6. 清理临时文件
            deleteDirectory(dir);
            new File(zipFilePath).delete();
            
            log.info("打包毕业档案成功：studentNo={}", studentNo);
        } catch (Exception e) {
            log.error("打包毕业档案失败", e);
            throw new BusinessException("打包失败：" + e.getMessage());
        }
    }
    
    // ========== 私有辅助方法 ==========
    
    private List<List<String>> buildSummaryHead() {
        List<List<String>> head = new ArrayList<>();
        head.add(Collections.singletonList("院系名称"));
        head.add(Collections.singletonList("在读人数"));
        head.add(Collections.singletonList("休学人数"));
        head.add(Collections.singletonList("毕业人数"));
        head.add(Collections.singletonList("退学人数"));
        head.add(Collections.singletonList("总计"));
        return head;
    }
    
    private List<List<String>> buildRecordHead() {
        List<List<String>> head = new ArrayList<>();
        head.add(Collections.singletonList("学号"));
        head.add(Collections.singletonList("姓名"));
        head.add(Collections.singletonList("记录类型"));
        head.add(Collections.singletonList("记录内容"));
        head.add(Collections.singletonList("记录时间"));
        return head;
    }
    
    private void exportStudentInfo(Student student, String dir) throws Exception {
        // 获取用户信息
        SysUser user = sysUserMapper.selectById(student.getUserId());
        
        // 构建表头
        List<List<String>> head = new ArrayList<>();
        head.add(Collections.singletonList("学号"));
        head.add(Collections.singletonList("姓名"));
        head.add(Collections.singletonList("性别"));
        head.add(Collections.singletonList("出生日期"));
        head.add(Collections.singletonList("手机号"));
        head.add(Collections.singletonList("家庭住址"));
        head.add(Collections.singletonList("入学日期"));
        head.add(Collections.singletonList("状态"));
        head.add(Collections.singletonList("备注"));
        
        // 构建数据
        List<List<Object>> data = new ArrayList<>();
        List<Object> row = new ArrayList<>();
        row.add(student.getStudentNo() != null ? student.getStudentNo() : "");
        row.add(user != null ? user.getRealName() : "");
        row.add(student.getGender() != null && student.getGender() == 1 ? "男" : "女");
        row.add(student.getBirthday() != null ? 
                student.getBirthday().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) : "");
        row.add(student.getPhone() != null ? student.getPhone() : "");
        row.add(student.getAddress() != null ? student.getAddress() : "");
        row.add(student.getEnrollDate() != null ? 
                student.getEnrollDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) : "");
        row.add(getStatusName(student.getStatus()));
        row.add(student.getRemark() != null ? student.getRemark() : "");
        data.add(row);
        
        String filePath = dir + File.separator + "学生信息.xlsx";
        EasyExcel.write(filePath)
                .head(head)
                .sheet("学生信息")
                .doWrite(data);
    }
    
    private void exportStudentScores(Long studentId, String dir) throws Exception {
        LambdaQueryWrapper<StudentScore> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(StudentScore::getStudentId, studentId);
        List<StudentScore> scores = studentScoreMapper.selectList(wrapper);
        
        if (!scores.isEmpty()) {
            String filePath = dir + File.separator + "成绩单.xlsx";
            EasyExcel.write(filePath, StudentScore.class).sheet("成绩单").doWrite(scores);
        }
    }
    
    private void exportStudentRecords(Long studentId, String dir) throws Exception {
        LambdaQueryWrapper<StudentRecord> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(StudentRecord::getStudentId, studentId);
        List<StudentRecord> records = studentRecordMapper.selectList(wrapper);
        
        if (!records.isEmpty()) {
            String filePath = dir + File.separator + "档案记录.xlsx";
            EasyExcel.write(filePath, StudentRecord.class).sheet("档案记录").doWrite(records);
        }
    }
    
    private void zipDirectory(String sourceDir, String zipFile) throws Exception {
        try (FileOutputStream fos = new FileOutputStream(zipFile);
             ZipOutputStream zos = new ZipOutputStream(fos)) {
            File dir = new File(sourceDir);
            zipFiles(dir, dir.getName(), zos);
        }
    }
    
    private void zipFiles(File file, String parentPath, ZipOutputStream zos) throws Exception {
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            if (files != null) {
                for (File f : files) {
                    zipFiles(f, parentPath + File.separator + f.getName(), zos);
                }
            }
        } else {
            try (FileInputStream fis = new FileInputStream(file)) {
                ZipEntry entry = new ZipEntry(parentPath);
                zos.putNextEntry(entry);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = fis.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }
                zos.closeEntry();
            }
        }
    }
    
    private void deleteDirectory(File dir) {
        if (dir.isDirectory()) {
            File[] files = dir.listFiles();
            if (files != null) {
                for (File f : files) {
                    deleteDirectory(f);
                }
            }
        }
        dir.delete();
    }
    
    private String getStatusName(Integer status) {
        if (status == null) return "未知";
        switch (status) {
            case 1: return "在读";
            case 2: return "休学";
            case 3: return "毕业";
            case 4: return "退学";
            default: return "未知";
        }
    }
    
    private String getRecordTypeName(Integer recordType) {
        if (recordType == null) return "未知";
        switch (recordType) {
            case 1: return "奖惩记录";
            case 2: return "异动记录";
            default: return "其他";
        }
    }
    
    /**
     * 生成学生导入失败数据文件
     */
    private String generateFailFileForStudent(List<StudentExcelDTO> failList, Long historyId) {
        try {
            // 创建失败数据目录
            String uploadDir = System.getProperty("user.dir") + File.separator + "uploads" + File.separator + "fail-data";
            File dir = new File(uploadDir);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            
            // 生成文件名
            String fileName = "student_fail_" + historyId + "_" + System.currentTimeMillis() + ".xlsx";
            String filePath = uploadDir + File.separator + fileName;
            
            // 写入Excel
            EasyExcel.write(filePath, StudentExcelDTO.class)
                .sheet("失败数据")
                .doWrite(failList);
            
            // 返回相对路径（用于下载）
            return "/uploads/fail-data/" + fileName;
            
        } catch (Exception e) {
            log.error("生成学生失败数据文件失败", e);
            return null;
        }
    }
    
    /**
     * 生成成绩导入失败数据文件
     */
    private String generateFailFileForScore(List<ScoreExcelDTO> failList, Long historyId) {
        try {
            // 创建失败数据目录
            String uploadDir = System.getProperty("user.dir") + File.separator + "uploads" + File.separator + "fail-data";
            File dir = new File(uploadDir);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            
            // 生成文件名
            String fileName = "score_fail_" + historyId + "_" + System.currentTimeMillis() + ".xlsx";
            String filePath = uploadDir + File.separator + fileName;
            
            // 写入Excel
            EasyExcel.write(filePath, ScoreExcelDTO.class)
                .sheet("失败数据")
                .doWrite(failList);
            
            // 返回相对路径（用于下载）
            return "/uploads/fail-data/" + fileName;
            
        } catch (Exception e) {
            log.error("生成成绩失败数据文件失败", e);
            return null;
        }
    }
    
    /**
     * 构建导入结果（学生）
     */
    private ImportResultDTO buildStudentImportResult(int successCount, List<StudentExcelDTO> failList, String failFileUrl) {
        ImportResultDTO result = new ImportResultDTO();
        result.setSuccessCount(successCount);
        result.setFailCount(failList.size());
        result.setTotalCount(successCount + failList.size());
        result.setFailFileUrl(failFileUrl);
        result.calculateSuccessRate();
        result.generateMessage();
        
        // 构建失败详情列表（最多显示前10条）
        int limit = Math.min(failList.size(), 10);
        for (int i = 0; i < limit; i++) {
            StudentExcelDTO failData = failList.get(i);
            ImportResultDTO.FailDetail detail = new ImportResultDTO.FailDetail();
            detail.setRowNumber(failData.getRowNumber());
            detail.setStudentNo(failData.getStudentNo());
            detail.setName(failData.getRealName());
            detail.setErrorReason(failData.getErrorReason());
            detail.setDataPreview(String.format("学号:%s, 姓名:%s, 班级:%s", 
                failData.getStudentNo(), failData.getRealName(), failData.getClassName()));
            result.getFailDetails().add(detail);
        }
        
        return result;
    }
    
    /**
     * 构建导入结果（成绩）
     */
    private ImportResultDTO buildScoreImportResult(int successCount, List<ScoreExcelDTO> failList, String failFileUrl) {
        ImportResultDTO result = new ImportResultDTO();
        result.setSuccessCount(successCount);
        result.setFailCount(failList.size());
        result.setTotalCount(successCount + failList.size());
        result.setFailFileUrl(failFileUrl);
        result.calculateSuccessRate();
        result.generateMessage();
        
        // 构建失败详情列表（最多显示前10条）
        int limit = Math.min(failList.size(), 10);
        for (int i = 0; i < limit; i++) {
            ScoreExcelDTO failData = failList.get(i);
            ImportResultDTO.FailDetail detail = new ImportResultDTO.FailDetail();
            detail.setRowNumber(failData.getRowNumber());
            detail.setStudentNo(failData.getStudentNo());
            detail.setName(failData.getStudentName());
            detail.setErrorReason(failData.getErrorReason());
            detail.setDataPreview(String.format("学号:%s, 姓名:%s, 课程:%s, 成绩:%s", 
                failData.getStudentNo(), failData.getStudentName(), failData.getCourseName(), failData.getScore()));
            result.getFailDetails().add(detail);
        }
        
        return result;
    }
}
